//
//  DataBaseHandler.m
//  SweetComics
//
//  Created by dlios on 15-2-3.
//  Copyright (c) 2015年 LeeZhe. All rights reserved.
//

#import "DataBaseHandler.h"

@implementation DataBaseHandler

+(DataBaseHandler *)shareInstance
{
    static DataBaseHandler *dataHandler = nil;
    if (dataHandler == nil) {
        dataHandler = [[DataBaseHandler alloc] init];
        [dataHandler openDB];
        [dataHandler createTable];
    }
    
    return dataHandler;
}

- (BOOL)judgeResult:(int)result name:(NSString *)name
{
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", name);
        return YES;
    }else
    {
        NSLog(@"%@失败, 错误代码: %d", name, result);
        return NO;
    }
}

- (void)openDB
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"comic.db"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    [self judgeResult:result name:@"打开数据库"];
}

- (void)closeDB
{
    int result = sqlite3_close(dbPoint);
    [self judgeResult:result name:@"关闭数据库"];
}

- (void)createTable
{
    NSString *sqlUser = [NSString stringWithFormat:@"create table user(name text primary key, email text, passWord text)"];
    int result1 = sqlite3_exec(dbPoint, [sqlUser UTF8String], NULL, NULL, NULL);
    [self judgeResult:result1 name:@"创建用户表"];
    
    NSString *sqlCollect = [NSString stringWithFormat:@"create table collectComic(comicName text primary key, comicId text)"];
    int result2 = sqlite3_exec(dbPoint, [sqlCollect UTF8String], NULL, NULL, NULL);
    
    [self judgeResult:result2 name:@"创建收藏表"];
    
    NSString *sqlLook = [NSString stringWithFormat:@"create table lookComic(title text primary key, comic_id text, cover_url text, artist_name text, grade text, lastup text, number integer, count integer)"];
    int result3 = sqlite3_exec(dbPoint, [sqlLook UTF8String], NULL, NULL, NULL);
    [self judgeResult:result3 name:@"创建看漫画表"];
}
// 收藏一条漫画数据
- (BOOL)insertComic:(ComicList *)comic
{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into collectComic values('%@','%@')", comic.title, comic.comic_id];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    BOOL flag = [self judgeResult:result name:@"收藏数据"];
    return flag;
}
// 订阅
- (void)glanceComic:(ComicList *)comic number:(NSInteger)number count:(NSInteger)count
{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into lookComic values('%@','%@','%@','%@','%@','%@', %ld, %ld)", comic.title, comic.comic_id, comic.cover_url, comic.artist_name, comic.grade, comic.lastup, (long)number, count];
//    NSString *sqlStr = [NSString stringWithFormat:@"insert into lookComic values('%@','%@','%@','%@','%@', %ld, %ld)", comic.title, comic.comic_id, comic.cover_url, comic.artist_name, comic.grade, number, count];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result name:@"浏览、订阅数据"];
}
// 查询所有数据
- (NSMutableArray *)selectAll
{
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, "select * from lookComic", -1, &stmt, NULL);
    NSMutableArray *arr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 提取每一行的数据
            // 参数1: stmt指针
            // 参数2: 第几列
            const unsigned char *titleChar = sqlite3_column_text(stmt, 0);
            
            NSString *title = [NSString stringWithUTF8String:(const char*)titleChar];
            const unsigned char *comicIdChar = sqlite3_column_text(stmt, 1);
            NSString *comic_id = [NSString stringWithUTF8String:(const char*)comicIdChar];
            
            const unsigned char *coverChar = sqlite3_column_text(stmt, 2);
            
            NSString *cover_url = [NSString stringWithUTF8String:(const char*)coverChar];
            
            const unsigned char *artistChar = sqlite3_column_text(stmt, 3);
            
            NSString *artist_name = [NSString stringWithUTF8String:(const char*)artistChar];
            
            const unsigned char *gradeChar = sqlite3_column_text(stmt, 4);
            
            NSString *grade = [NSString stringWithUTF8String:(const char*)gradeChar];
            
            const unsigned char *lastChar = sqlite3_column_text(stmt, 5);
            
            NSString *lastup = [NSString stringWithUTF8String:(const char*)lastChar];
            
            NSInteger number = sqlite3_column_int(stmt, 6);
            NSInteger count = sqlite3_column_int(stmt, 7);
            // 创建一个漫画对象用来接受数据
            ComicList *comic = [[ComicList alloc] init];
            comic.title = title;
            comic.comic_id = comic_id;
            comic.cover_url = cover_url;
            comic.artist_name = artist_name;
            comic.grade = grade;
            comic.lastup = lastup;
            comic.number = number;
            comic.countPage = count;
            [arr addObject:comic];
            [comic release];
        }
        
        
    }
    // 清空stmt中的数据
    sqlite3_finalize(stmt);
    return arr;
    
}

//
- (NSMutableArray *)selectCollect
{
    sqlite3_stmt *stmt = nil;
    // 2. 把stmt指针指向本地数据库
    
    int result = sqlite3_prepare_v2(dbPoint, "select * from collectComic", -1, &stmt, NULL);
    // 3. 把有用的数据从stmt中 提取出来
    // 创建一个空的可变数组, 用来装student数据
    NSMutableArray *arr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *titleChar = sqlite3_column_text(stmt, 0);
            
            NSString *title = [NSString stringWithUTF8String:(const char*)titleChar];
            const unsigned char *comicIdChar = sqlite3_column_text(stmt, 1);
            NSString *comic_id = [NSString stringWithUTF8String:(const char*)comicIdChar];
            
            // 创建一个漫画对象用来接受数据
            ComicList *comic = [[ComicList alloc] init];
            comic.title = title;
            comic.comic_id = comic_id;
            [arr addObject:comic];
            [comic release];
        }
    }
    // 清空stmt中的数据
    sqlite3_finalize(stmt);
    return arr;
}


// 修改一条数据
- (void)updateUser:(User *)user
{
    NSString *sqlStr = [NSString stringWithFormat:@"update user set passWord = %@ where name = %@", user.passWord, user.name];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result name:@"修改数据"];
}
// 删除一条数据
- (void)deleteComic:(ComicList *)comic
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from lookComic where title = '%@'", comic.title];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result name:@"删除数据"];
}

- (void)deleteCollect:(ComicList *)comic
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from collectComic where comicName = '%@'", comic.title];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result name:@"删除数据"];
}
@end